create or replace function user_exists(p_email varchar(64))
    returns text as
$$
begin
    if exists (select * from users where email=lower(p_email)) then
        return 'true';
    end if;
    return 'false';
end;
$$
language plpgsql;

create or replace function delete_user(p_email varchar(64))
    returns void as
$$
begin
    delete from users where email = lower(p_email);
end;
$$
language plpgsql;

create or replace function upsert_user(p_email varchar(64), p_hashedPassword varchar(128), p_isAdmin boolean)
    returns void as
$$
begin
    loop
        -- first try to update the key
        update users set
                hashedPassword = p_hashedPassword,
                isAdmin = p_isAdmin
            where email = lower(p_email);
        if found then
            return;
        end if;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        begin
            insert into users(email, hashedPassword, isAdmin)
                values (lower(p_email), p_hashedPassword, p_isAdmin);
            return;
        exception when unique_violation then
            -- do nothing, and loop to try the UPDATE again
        end;
    end loop;
end;
$$
language plpgsql;

create or replace function get_user_by_email(p_email varchar(64))
    returns text as
$$
begin
    return row_to_json(users) from users where email=lower(p_email);
end;
$$
language plpgsql;
